package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.UserBuy;
import util.DbUtil;

public class UserBuyDao {
	
	public int addUserBuy(UserBuy userbuy){
	    int flag=0;
		DbUtil dbutil=new DbUtil();
		Connection con = dbutil.dbcon();
		String sqls="insert into userbuy (programId,red_1,red_2,red_3,red_4,red_5,red_6,blue) value (?,?,?,?,?,?,?,?)";
		PreparedStatement ps=null;
		try {
			ps=con.prepareStatement(sqls);
			ps.setInt(1, userbuy.getProgramId());
			ps.setInt(2, userbuy.getRed_1());
			ps.setInt(3, userbuy.getRed_2());
			ps.setInt(4, userbuy.getRed_3());
			ps.setInt(5, userbuy.getRed_4());
			ps.setInt(6, userbuy.getRed_5());
			ps.setInt(7, userbuy.getRed_6());
			ps.setInt(8, userbuy.getBlue());
			flag=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		dbutil.closecon(ps, null);
		return flag;		
	}
	
	public int addMarry(int userId,int marryNum,int marryBlue,int weight){
	    int flag=0;
		DbUtil dbutil=new DbUtil();
		Connection con = dbutil.dbcon();
		String sqls="update userbuy set marryNum=?,marryBlue=?,weight=? where userId=?";
		PreparedStatement ps=null;
		try {
			ps=con.prepareStatement(sqls);
			ps.setInt(1, marryNum);
			ps.setInt(2, marryBlue);	
			ps.setInt(3,weight);
			ps.setInt(4, userId);
			flag=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbutil.closecon(ps, null);
		return flag;
	}
	
	//找出所有用户并且匹配
	public ArrayList<UserBuy> findAllUserBuy(){
		ArrayList<UserBuy> listUserBuy=new ArrayList<UserBuy>();
		DbUtil dbutil=new DbUtil();
		Connection con = dbutil.dbcon();
		String sqls="select * from userbuy";
		Statement stat = null;
		try {
			stat=con.createStatement();
			ResultSet rs=stat.executeQuery(sqls);
			while(rs.next()){
				UserBuy userbuy=new UserBuy();
				userbuy.setUserId(rs.getInt("userId"));
				userbuy.setProgramId(rs.getInt("programId"));
				userbuy.setRed_1(rs.getInt("red_1"));
				userbuy.setRed_2(rs.getInt("red_2"));
				userbuy.setRed_3(rs.getInt("red_3"));
				userbuy.setRed_4(rs.getInt("red_4"));
				userbuy.setRed_5(rs.getInt("red_5"));
				userbuy.setRed_6(rs.getInt("red_6"));
				userbuy.setBlue(rs.getInt("blue"));
				listUserBuy.add(userbuy);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbutil.closecon(null, stat);
		return listUserBuy;		
	} 
	
	//查找出幸运值前10用户
	public ArrayList<UserBuy> findTopTen(){
		ArrayList<UserBuy> listUserBuy=new ArrayList<UserBuy>();
		DbUtil dbutil=new DbUtil();
		Connection con = dbutil.dbcon();
		String sqls="select * from userbuy order by weight desc limit 10";
		Statement stat = null;
		try {
			stat=con.createStatement();
			ResultSet rs=stat.executeQuery(sqls);
			while(rs.next()){
				UserBuy userbuy=new UserBuy();
				userbuy.setUserId(rs.getInt("userId"));
				userbuy.setProgramId(rs.getInt("programId"));
				userbuy.setRed_1(rs.getInt("red_1"));
				userbuy.setRed_2(rs.getInt("red_2"));
				userbuy.setRed_3(rs.getInt("red_3"));
				userbuy.setRed_4(rs.getInt("red_4"));
				userbuy.setRed_5(rs.getInt("red_5"));
				userbuy.setRed_6(rs.getInt("red_6"));
				userbuy.setBlue(rs.getInt("blue"));
				userbuy.setMarryNum(rs.getInt("marryNum"));
				userbuy.setMarryBlue(rs.getInt("marryBlue"));
				userbuy.setWeight(rs.getInt("weight"));			
				listUserBuy.add(userbuy);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbutil.closecon(null, stat);
		return listUserBuy;						
	}
}
